﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WinformTest
{
    public partial class Form1 : Form
    {
        private byte[] downloadedData;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            
        }

        
        #region Form events
        private void btnDownload_Click(object sender, EventArgs e)
        {
            string Address, filename, user, pass;
            Address = "ftp://ftp.byethost11.com/htdocs";
            filename = "TestData.xlsx";
            user = "b11_12818818";
            pass = "phuong";
            downloadFile(Address,filename,user,pass);
            DataSet dsData = new DataSet();
            dsData = ReadExcel();
            grvData.DataSource = dsData.Tables[0];
            
        }

        private void btnUpload_Click(object sender, EventArgs e)
        {
            DataTable dtData = new DataTable();
            dtData.Columns.Add("Name");
            dtData.Columns.Add("Address");
            dtData.Rows.Add("t1", "addr1");
            dtData.Rows.Add("t2", "addr2");

            UploadtoDB(dtData);
        }
        #endregion

        #region Methods
        private void downloadFile(string FTPAddress, string filename, string username, string password)
        {
            downloadedData = new byte[0];

            try
            {
                //Optional
                //this.Text = "Connecting...";
                //Application.DoEvents();

                ////Create FTP request
                ////Note: format is ftp://server.com/file.ext
                //FtpWebRequest request = FtpWebRequest.Create(FTPAddress + "/" + filename) as FtpWebRequest;

                ////Optional
                //this.Text = "Retrieving Information...";
                //Application.DoEvents();

                ////Get the file size first (for progress bar)
                //request.Method = WebRequestMethods.Ftp.GetFileSize;
                //request.Credentials = new NetworkCredential(username, password);
                //request.UsePassive = true;
                //request.UseBinary = true;
                //request.KeepAlive = true; //don't close the connection

                //int dataLength = (int)request.GetResponse().ContentLength;

                //Optional
                lblStatus.Text = "Downloading File...";
                Application.DoEvents();

                //Now get the actual data
                FtpWebRequest request = FtpWebRequest.Create(FTPAddress + "/" + filename) as FtpWebRequest;
                request.Method = WebRequestMethods.Ftp.DownloadFile;
                request.Credentials = new NetworkCredential(username, password);
                request.UsePassive = true;
                request.UseBinary = true;
                request.KeepAlive = false; //close the connection when done

                //Set up progress bar
                //progressBar1.Value = 0;
                //progressBar1.Maximum = dataLength;
                //lbProgress.Text = "0/" + dataLength.ToString();

                //Streams
                FtpWebResponse response = request.GetResponse() as FtpWebResponse;
                Stream reader = response.GetResponseStream();
                string sFilePathToWriteFileTo = string.Format("{0}\\TestData.xlsx", Directory.GetCurrentDirectory());
                //Download to memory
                //Note: adjust the streams here to download directly to the hard drive
                using (FileStream memStream = new FileStream(sFilePathToWriteFileTo, FileMode.Create))
                {
                    byte[] buffer = new byte[1024]; //downloads in chuncks                

                    while (true)
                    {
                        Application.DoEvents(); //prevent application from crashing

                        //Try to read the data
                        int bytesRead = reader.Read(buffer, 0, buffer.Length);
                       

                        if (bytesRead == 0)
                        {
                            //Nothing was read, finished downloading
                            //progressBar1.Value = progressBar1.Maximum;
                            //lbProgress.Text = dataLength.ToString() + "/" + dataLength.ToString();

                            Application.DoEvents();
                            break;
                        }
                        else
                        {
                            //Write the downloaded data
                            memStream.Write(buffer, 0, bytesRead);

                            //Update the progress bar
                            //if (progressBar1.Value + bytesRead <= progressBar1.Maximum)
                            //{
                            //    progressBar1.Value += bytesRead;
                            //    lbProgress.Text = progressBar1.Value.ToString() + "/" + dataLength.ToString();

                            //    progressBar1.Refresh();
                            //    Application.DoEvents();
                            //}
                        }
                    }
                    memStream.Close();
                }
                //Convert the downloaded stream to a byte array


                //Clean up
                reader.Close();
                response.Close();

                MessageBox.Show("Downloaded Successfully");
            }
            catch (Exception ex)
            {
                MessageBox.Show("There was an error connecting to the FTP Server.");
            }

            //txtData.Text = downloadedData.Length.ToString();
            lblStatus.Text = "Download Data through FTP";

            username = string.Empty;
            password = string.Empty;
        }
        public DataSet ReadExcel()
        {
            DataSet dsData = new DataSet();
            var fileName = string.Format("{0}\\TestData.xlsx", Directory.GetCurrentDirectory());

            //var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", fileName);
            var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);

            adapter.Fill(dsData, "Data");

            return dsData;
        }
        public void UploadtoDB(DataTable dtValue)
        {
            DataTable dtData = new DataTable();

            string connectionString = string.Format("Server=PHUONG-PC\\SQLEXPRESS;Database=Student;User Id=sa;Password=123;");
            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.InsertCommand = new SqlCommand("Insert into Students values(@Name,@Address)", conn);
                adapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
                adapter.InsertCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 200, "Address");

                //adapter.Fill(dtValue);
                adapter.Update(dtValue);

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}
